from django.http import JsonResponse
import pymssql
from collections import defaultdict
from django.utils import timezone
from datetime import datetime, timedelta
import json
from rest_framework.views import APIView


class ExternalSQLAPIView(APIView):
    def get(self, request, format=None):
        default_start_date = (timezone.now().date() - timedelta(days=7)).isoformat()
        default_end_date = timezone.now().date().isoformat()
        # 连接数据库
        conn = pymssql.connect(server='rm-2zeo43td829q4e59apo.sqlserver.rds.aliyuncs.com', user='superadmin',
                               password='QYZHjhsz0628#', database='ABSO_DB')
        # 创建cursor对象
        cursor = conn.cursor()
        # 编写SQL查询语句
        #sql_query = "SELECT TOP 2 * FROM [dbo].[__EFMigrationsHistory] ORDER BY MigrationId DESC"
        sql_query = "select Code,TotalPrice,CreateTime from [Order] where CreateTime >='2024-12-01' and CreateTime <'2024-12-11'"
        cursor.execute(sql_query)
        orders = cursor.fetchall()

        # 关闭游标和连接
        cursor.close()
        conn.close()

        daily_data = defaultdict(lambda: {'order_count': 0, 'total_price': 0.0, 'avg_price': 0.0})

        data_points = []
        for order in orders:
            date_str = order['CreateTime'].date().isoformat()
            daily_data[date_str]['order_count'] += 1
            daily_data[date_str]['total_price'] += float(order['TotalPrice'])

        # 计算平均价格
        for date_str, stats in daily_data.items():
            if stats['order_count'] > 0:
                stats['avg_price'] = round(stats['total_price'] / stats['order_count'], 2)
            else:
                stats['avg_price'] = 0.0

            data_points.append({
                'date': date_str,
                'data': {
                    'Order Count': stats['order_count'],
                    'Total Price': stats['total_price'],
                    'Avg Price': stats['avg_price']
                }
            })

        # 返回JSON响应
        return JsonResponse({'data': data_points}, safe=False)

